package sqlmaker

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"os"
	"reflect"
	"strings"
	"local.dev/sqlmaker/config"
	_ "github.com/go-sql-driver/mysql"
	"time"
)

type QueryBuilder struct {
	queryType string
	table     string
	columns   []string
	values    []interface{}
	where     []string
	args      []interface{}
}

func NewQueryBuilder() *QueryBuilder {
	return &QueryBuilder{}
}

func (qb *QueryBuilder) InsertInto(table string) *QueryBuilder {
	qb.queryType = "INSERT"
	qb.table = table
	return qb
}

func (qb *QueryBuilder) Columns(columns ...string) *QueryBuilder {
	qb.columns = columns
	return qb
}

func (qb *QueryBuilder) Values(values ...interface{}) *QueryBuilder {
	qb.values = values
	return qb
}

func (qb *QueryBuilder) Where(condition string, args ...interface{}) *QueryBuilder {
	qb.where = append(qb.where, condition)
	qb.args = append(qb.args, args...)
	return qb
}

func (qb *QueryBuilder) SelectFrom(table string) *QueryBuilder {
	qb.queryType = "SELECT"
	qb.table = table
	return qb
}

func (qb *QueryBuilder) Build() (string, []interface{}) {
	switch qb.queryType {
	case "SELECT":
		fields := "*"
		if len(qb.columns) > 0 {
			fields = strings.Join(qb.columns, ", ")
		}
		query := fmt.Sprintf("SELECT %s FROM %s", fields, qb.table)
		if len(qb.where) > 0 {
			query += " WHERE " + strings.Join(qb.where, " AND ")
		}
		return query, qb.args
	case "INSERT":
		columns := strings.Join(qb.columns, ", ")
		placeholders := strings.Repeat("?, ", len(qb.values))
		placeholders = placeholders[:len(placeholders)-2]
		return fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s)", qb.table, columns, placeholders), qb.values
	default:
		return "", nil
	}
}

type DB struct {
	db *sql.DB
}

func (d *DB) Connect(cfg *config.Config) error {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s", cfg.Database.User, cfg.Database.Password, cfg.Database.Host, cfg.Database.Port, cfg.Database.DBName)
	db, err := sql.Open(cfg.Database.Driver, dsn)
	if err != nil {
		return err
	}
	d.db = db
	return nil
}

func Connect(cfg *config.Config) (*DB, error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s&parseTime=True&loc=Local",
		cfg.Database.User,
		cfg.Database.Password,
		cfg.Database.Host,
		cfg.Database.Port,
		cfg.Database.DBName,
		cfg.Database.Charset)

	dbConn, err := sql.Open(cfg.Database.Driver, dsn)
	if err != nil {
		return nil, fmt.Errorf("连接失败: %v", err)
	}

	dbConn.SetMaxOpenConns(25)
	dbConn.SetMaxIdleConns(25)
	dbConn.SetConnMaxLifetime(5 * time.Minute)

	if err = dbConn.Ping(); err != nil {
		return nil, fmt.Errorf("连接验证失败: %v", err)
	}
	return &DB{db: dbConn}, nil
}

func (d *DB) Query(sql string, args ...interface{}) (*sql.Rows, error) {
	return d.db.Query(sql, args...)
}

func (d *DB) Exec(query string, args ...interface{}) (sql.Result, error) {
	return d.db.Exec(query, args...)
}
func (d *DB) Close() error {
	return d.db.Close()
}

type Mappable interface {
	Scan(rows *sql.Rows) error
}

func ScanRows(rows *sql.Rows, dest Mappable) error {
	return dest.Scan(rows)
}

func ScanAll(rows *sql.Rows, destSlice interface{}) error {
	defer rows.Close()

	destValue := reflect.ValueOf(destSlice).Elem()
	elemType := destValue.Type().Elem().Elem()

	for rows.Next() {
		elem := reflect.New(elemType).Interface().(Mappable)
		if err := elem.Scan(rows); err != nil {
			return err
		}
		destValue.Set(reflect.Append(destValue, reflect.ValueOf(elem).Elem()))
	}
	return rows.Err()
}

func WriteToFile(data interface{}, filename string) error {
	file, _ := os.Create(filename)
	defer file.Close()
	return json.NewEncoder(file).Encode(data)
}

type User struct {
	ID   int    `db:"id"`
	Name string `db:"name"`
	Age  int    `db:"age"`
}

func (u *User) Scan(rows *sql.Rows) error {
	return rows.Scan(&u.ID, &u.Name, &u.Age)
}

func (qb *QueryBuilder) Query(db *DB) (*sql.Rows, error) {
	query, args := qb.Build()
	return db.Query(query, args...)
}

func QueryWithMapping(db *DB, qb *QueryBuilder) ([]User, error) {
	var users []User
	rows, _ := qb.Query(db)
	defer rows.Close()

	for rows.Next() {
		var u User
		if err := ScanRows(rows, &u); err != nil {
			return nil, err
		}
		users = append(users, u)
	}
	return users, nil
}